package vlms.dao;

import java.sql.*;
import java.util.*;

import vlms.bean.Account;
import vlms.exception.AccountException;
import vlms.util.Encryption;

public class AccountDAO {

	static HashMap<Integer, Account> listByPersonId;
	static HashMap<String, Account> listByUsername;

	//Static Method Starts
	private static void staticPopulateVO(Account dto, ResultSet rs) throws SQLException {
		dto.setUsername(rs.getString("userName"));
		dto.setPassword(rs.getString("password"));
		dto.setPersonid(rs.getInt("personId"));
	}
	
	private static void initializeList(Connection con)
	{		
		try {
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			listByPersonId = new HashMap<Integer,Account>();
			listByUsername = new HashMap<String,Account>();
			
			String SQL_STATEMENT = "Select * from Account";
			stmt = con.prepareStatement(SQL_STATEMENT);
			rs = stmt.executeQuery();

			
			while (rs.next()) {
				Account dto = new Account();
				staticPopulateVO(dto, rs);					
				listByPersonId.put(dto.getPersonid(), dto);
				listByUsername.put(dto.getUsername(), dto);
			}				
		}			
		catch(SQLException se) {
		
			// The exception cannot be navigated back to the client. 
			// but as the initialization of list is failed. It will just
			// be printed as a error in the server console. This indicates
			// now the list is useless and every query is going to database 
			// instead of fetching from the list.
			System.out.println("Exception "+se+ " occured in the system." +
							   " Failed to initialize the list.");
			
		}
	}
	
	static 
	{
		Connection con = null;
		try {
			con = DriverManager.getConnection("jdbc:user_mysql:");
			initializeList(con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//Static Method Ends
	
	//Find fields which are updated after last read
	private HashMap<String,Object> findUpdatedFields(Account newAccountObject, Account listAccountObject) {
		
		HashMap<String,Object> updatedFieldsHashMap = new HashMap<String,Object>(); 
		
		if(!newAccountObject.getUsername().equals(listAccountObject.getUsername())) {
			updatedFieldsHashMap.put("userName", newAccountObject.getUsername());
		}
		
		String encryptedPassword = Encryption.encrypt(newAccountObject.getPassword(), "MD5", "UTF8"); 
		
		if(!encryptedPassword.equals(listAccountObject.getPassword())) {
			updatedFieldsHashMap.put("password", encryptedPassword);
		}
 		
		
		return updatedFieldsHashMap;
		
	}
	//Function Ends
	
	
	public int delete(String userName, Connection con) throws AccountException, SQLException {
			
			PreparedStatement ps = null;			
			ps = con.prepareStatement("delete from  ACCOUNT where userName = ?");
			ps.setString(1, userName);
			//this.wwait
			//Account.class.
			int rowsUpdated = ps.executeUpdate();
			if(rowsUpdated <= 0) {
				throw new AccountException("No account found in the system");
			}
			else{
				
				listByPersonId.remove(listByUsername.get(userName).getPersonid());
				listByUsername.remove(userName);
				
			}
			return (rowsUpdated);		
	}

	public int update(int personId, Account account, Connection con) throws AccountException, SQLException {
		PreparedStatement ps = null;
							
		
		String sqlQuery;		
		int rowsUpdated = 0;
		
		sqlQuery = "update ACCOUNT set userName = '"+ account.getUsername() + "' WHERE personID = ?";
		ps = con.prepareStatement(sqlQuery);
		ps.setInt(1, account.getPersonid());
		rowsUpdated = ps.executeUpdate();

		listByPersonId.put(account.getPersonid(), account);
		listByUsername.put(account.getUsername(), account);
		return (rowsUpdated);
	}
	public int updatePassword(int personId, Account account, Connection con) throws AccountException, SQLException {
		PreparedStatement ps = null;
							
		
		String sqlQuery;		
		int rowsUpdated = 0;
		
		sqlQuery = "update ACCOUNT set password = '"+ account.getPassword() + "' WHERE personID = ?";
		ps = con.prepareStatement(sqlQuery);
		ps.setInt(1, personId);
		rowsUpdated = ps.executeUpdate();
		
		listByPersonId.put(account.getPersonid(), account);
		listByUsername.put(account.getUsername(), account);		
		return (rowsUpdated);
	}
	
	public int insert(Account account, Connection con) throws AccountException, SQLException {

		PreparedStatement ps = null;
						
		ps = con.prepareStatement("insert into ACCOUNT(USERNAME, PASSWORD, PERSONID) values (?, ?, ?)");
		ps.setString(1, account.getUsername());
		ps.setString(2, Encryption.encrypt(account.getPassword(),"MD5", "UTF8"));
		ps.setInt(3, account.getPersonid());
		
		int rowsUpdated = ps.executeUpdate();
		if(rowsUpdated <= 0) {
			throw new AccountException("Account already exists");
		}
		else {
			listByPersonId.put(account.getPersonid(), account);
		}
			
		return (rowsUpdated);
	}

	public Account findByPrimaryKey(String username, Connection con) throws AccountException,SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		Account oAccount = listByUsername.get(username);
		
		if(oAccount == null) {
		
			final String SQLSTATEMENT = "Select userName, password, personId from account where userName = ?";
			stmt = con.prepareStatement(SQLSTATEMENT);
			stmt.setString(1, username);
			rs = stmt.executeQuery();
		
			oAccount = fetchSingleResult(rs);
		
		}
		if(oAccount == null) {
			throw new AccountException("Account does not exist");
		}
		
		return oAccount;
	
	}
	
	public Account findAccount(Account account, Connection con) throws AccountException,SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		Account oAccount = listByUsername.get(account.getUsername());
		// Harshil TODO : Check the database if not found in list 
		
		if(oAccount!= null) {
			if(oAccount.getPassword().equals(Encryption.encrypt(account.getPassword(), "MD5", "UTF8"))) {
				return oAccount;
			}
			else
				throw new AccountException("Invalid Username/Password");
		}
		
		if(oAccount == null) {
		
			final String SQLSTATEMENT = "Select userName, password, personId from account where userName = ?";
			stmt = con.prepareStatement(SQLSTATEMENT);
			stmt.setString(1, account.getUsername());
			rs = stmt.executeQuery();
		
			oAccount = fetchSingleResult(rs);
		
		}
		if(oAccount == null) {
			throw new AccountException("Invalid Username/Password");
		}
		
		return oAccount;
	
	}

	//Why this function is required
	public Account[] findByUsername(String username, Connection con) throws AccountException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String SQL_STATEMENT = "Select userName, password, personId from account where userName = ? order by userName";
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			stmt.setString(1, username);
			rs = stmt.executeQuery();
			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new AccountException(sqle);
		} catch (Exception e) {
			throw new AccountException(e);
		} finally {
		}
	}

	//Why this function is required
	public Account[] findByPersonid(int personid, Connection con) throws AccountException {
		PreparedStatement stmt = null;
		ResultSet rs = null;		
		
		String SQL_STATEMENT = "Select userName, password, personId from account where personId = ? order by personId";
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			stmt.setInt(1, personid);
			rs = stmt.executeQuery();
			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new AccountException(sqle);
		} catch (Exception e) {
			throw new AccountException(e);
		} finally {
		}
	}

	public Account[] findAll(Connection con) throws AccountException, SQLException {
		
		
		Account[] aAccount = new Account[listByPersonId.size()];
		Set<Integer> keys = listByPersonId.keySet();
		
		Iterator<Integer> it = keys.iterator();
		int i=0;
		
		while(it.hasNext()) {			
			aAccount[i] = listByPersonId.get(it.next()); 
		}
		
		if(aAccount.length == 0) {
			throw new AccountException("No account found");
		}
		return aAccount;
	}

	public Account[] findExecutingUserSelect(String selectStatement,Object[] sqlParams, Connection con) throws AccountException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		final String SQL_STATEMENT = selectStatement;
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			for (int i = 0; i < sqlParams.length; i++) {
				stmt.setObject(i + 1, sqlParams[i]);
			}
			rs = stmt.executeQuery();
			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new AccountException(sqle);
		} catch (Exception e) {
			throw new AccountException(e);
		} finally {
		}
	}

	public Account[] findExecutingUserWhere(String whereClause,
			Object[] sqlParams, Connection con) throws AccountException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String SQL_SELECT = "Select userName, password, personId from account";
		final String SQL_STATEMENT = SQL_SELECT + " where " + whereClause;
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			for (int i = 0; i < sqlParams.length; i++) {
				stmt.setObject(i + 1, sqlParams[i]);
			}
			rs = stmt.executeQuery();
			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new AccountException(sqle);
		} catch (Exception e) {
			throw new AccountException(e);
		} finally {
		}
	}

	protected Account fetchSingleResult(ResultSet rs) throws SQLException {
		if (rs.next()) {
			Account dto = new Account();
			populateVO(dto, rs);
			return dto;
		} else {
			return null;
		}
	}

	protected void populateVO(Account dto, ResultSet rs) throws SQLException {
		dto.setUsername(rs.getString("userName"));
		dto.setPassword(rs.getString("password"));
		dto.setPersonid(rs.getInt("personId"));
	}

	protected Account[] fetchMultiResults(ResultSet rs) throws SQLException {
		ArrayList<Account> resultList = new ArrayList<Account>();
		while (rs.next()) {
			Account dto = new Account();
			populateVO(dto, rs);
			resultList.add(dto);
		}
		Account ret[] = new Account[resultList.size()];
		resultList.toArray(ret);
		return ret;
	}
	
	public boolean isValid(Account oAccount) throws AccountException {
		String userName = oAccount.getUsername();
		String password = oAccount.getPassword();
		
		if(userName == null || userName.length() < 8 || userName.length() > 45) {
			throw new AccountException("Invalid Username Format. User Name should be of more than 8 characters and less than 45 character");
		}
		if(password == null || !password.matches("((?=.*[0-9])(?=.*[A-Z])(?=.*[@#$%]).{8,45})")) {
			throw new AccountException("Invalid Password Format. It should contain one Upper, one Lower, One symbol and one numeric character.");
		}
		return true;
	}
	
	public boolean isExist(Account account,Connection con) throws AccountException, SQLException {
		// TODO Auto-generated method stub
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Account result;
		
		result = listByUsername.get(account.getUsername());
		
		if (result != null)
		{
			throw new AccountException("Username already exists. Please provide another Username");
		}
		String SQL_STATEMENT = "Select count(username) as accountCount from account where username = ?";
		stmt = con.prepareStatement(SQL_STATEMENT);
		stmt.setString(1, account.getUsername());
		rs = stmt.executeQuery();
		rs.next();
		if(rs.getInt("accountCount") != 0) {
			throw new AccountException("Username already exists. Please provide another Username");
		}		
		return false;
	}
}
